--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;


DROP DATABASE IF EXISTS activerecorddb;
CREATE DATABASE activerecorddb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'Italian_Italy.1252' LC_CTYPE = 'Italian_Italy.1252';

ALTER DATABASE activerecorddb OWNER TO postgres;

\connect activerecorddb

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 202 (class 1259 OID 58853)
-- Name: articles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.articles (
    id bigint NOT NULL,
    description character varying(100) NOT NULL,
    price integer NOT NULL
);


ALTER TABLE public.articles OWNER TO postgres;

--
-- TOC entry 203 (class 1259 OID 58856)
-- Name: articles_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.articles_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.articles_id_seq OWNER TO postgres;

--
-- TOC entry 2911 (class 0 OID 0)
-- Dependencies: 203
-- Name: articles_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.articles_id_seq OWNED BY public.articles.id;


--
-- TOC entry 220 (class 1259 OID 58961)
-- Name: complex_types; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.complex_types (
    id bigint NOT NULL,
    json_field json,
    jsonb_field jsonb,
    xml_field xml
);


ALTER TABLE public.complex_types OWNER TO postgres;

--
-- TOC entry 219 (class 1259 OID 58959)
-- Name: complex_types_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.complex_types ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.complex_types_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 204 (class 1259 OID 58858)
-- Name: customers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customers (
    id bigint NOT NULL,
    code character varying(20),
    description character varying(200),
    city character varying(200),
    note text,
    rating integer,
	last_contact_timestamp timestamptz NULL
);

ALTER TABLE public.customers OWNER TO postgres;


CREATE TABLE public.customers2 (
    id bigint generated by default as identity NOT NULL,
    code character varying(20),
    description character varying(200),
    city character varying(200),
    note text,
    rating integer,
	last_contact_timestamp timestamptz NULL
);

ALTER TABLE public.customers2 OWNER TO postgres;


CREATE TABLE public.customers_with_version (
    id bigint generated by default as identity NOT NULL,
    code character varying(20),
    description character varying(200),
    city character varying(200),
    note text,
    rating integer,
	objversion integer
);

ALTER TABLE public.customers_with_version OWNER TO postgres;

--
-- TOC entry 205 (class 1259 OID 58864)
-- Name: customers with spaces; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public."customers with spaces" (
    "id with spaces" bigint NOT NULL,
    "code with spaces" character varying(20),
    "description with spaces" character varying(200),
    "city with spaces" character varying(200),
    "note with spaces" text,
    "rating with spaces" integer
);


ALTER TABLE public."customers with spaces" OWNER TO postgres;

--
-- TOC entry 206 (class 1259 OID 58870)
-- Name: customers_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.customers_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.customers_id_seq OWNER TO postgres;

--
-- TOC entry 2912 (class 0 OID 0)
-- Dependencies: 206
-- Name: customers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.customers_id_seq OWNED BY public.customers.id;


--
-- TOC entry 207 (class 1259 OID 58872)
-- Name: customers_plain; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customers_plain (
    id bigint NOT NULL,
    code character varying(20),
    description character varying(200),
    city character varying(200),
    note text,
    rating integer,
    creation_time time without time zone,
    creation_date date
);


ALTER TABLE public.customers_plain OWNER TO postgres;

--
-- TOC entry 208 (class 1259 OID 58878)
-- Name: customers_with_code; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.customers_with_code (
    code character varying(20) NOT NULL,
    description character varying(200),
    city character varying(200),
    note text,
    rating integer
);


ALTER TABLE public.customers_with_code OWNER TO postgres;


-- public.customers_with_guid definition

-- Drop table

-- DROP TABLE public.customers_with_guid;

CREATE TABLE public.customers_with_guid (
	idguid uuid NOT NULL,
	code varchar(20) NULL,
	description varchar(200) NULL,
	city varchar(200) NULL,
	note text NULL,
	rating int4 NULL,
	CONSTRAINT customers_with_guid_pk PRIMARY KEY (idguid)
);


--
-- TOC entry 209 (class 1259 OID 58884)
-- Name: nullables_test; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nullables_test (
    f_int2 smallint,
    f_int8 bigint,
    f_int4 integer,
    f_string character varying,
    f_bool boolean,
    f_date date,
    f_time time without time zone,
    f_datetime timestamp without time zone,
    f_float4 real,
    f_float8 double precision,
    f_currency numeric(18,4),
    f_blob bytea
);


ALTER TABLE public.nullables_test OWNER TO postgres;

--
-- TOC entry 210 (class 1259 OID 58890)
-- Name: order_details; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.order_details (
    id bigint NOT NULL,
    id_order bigint NOT NULL,
    id_article bigint NOT NULL,
    unit_price numeric(18,2) NOT NULL,
    discount integer DEFAULT 0 NOT NULL,
    quantity integer NOT NULL,
    description character varying(200) NOT NULL,
    total numeric(18,2) NOT NULL
);


ALTER TABLE public.order_details OWNER TO postgres;

--
-- TOC entry 211 (class 1259 OID 58894)
-- Name: order_details_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.order_details_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.order_details_id_seq OWNER TO postgres;

--
-- TOC entry 2913 (class 0 OID 0)
-- Dependencies: 211
-- Name: order_details_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.order_details_id_seq OWNED BY public.order_details.id;


--
-- TOC entry 212 (class 1259 OID 58896)
-- Name: orders; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.orders (
    id bigint NOT NULL,
    id_customer integer NOT NULL,
    order_date date NOT NULL,
    total numeric(18,4)
);


ALTER TABLE public.orders OWNER TO postgres;

--
-- TOC entry 213 (class 1259 OID 58899)
-- Name: orders_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.orders_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.orders_id_seq OWNER TO postgres;

--
-- TOC entry 2914 (class 0 OID 0)
-- Dependencies: 213
-- Name: orders_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.orders_id_seq OWNED BY public.orders.id;


--
-- TOC entry 214 (class 1259 OID 58901)
-- Name: people; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.people (
    id bigint NOT NULL,
    last_name character varying(100) NOT NULL,
    first_name character varying(100) NOT NULL,
    dob date,
    full_name character varying(80),
    is_male boolean,
    note text,
    photo bytea,
	person_type varchar(40),
	salary money,
	annual_bonus money	
);


ALTER TABLE public.people OWNER TO postgres;

--
-- TOC entry 215 (class 1259 OID 58907)
-- Name: people_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.people_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.people_id_seq OWNER TO postgres;

--
-- TOC entry 2915 (class 0 OID 0)
-- Dependencies: 215
-- Name: people_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.people_id_seq OWNED BY public.people.id;


--
-- TOC entry 216 (class 1259 OID 58909)
-- Name: phones; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.phones (
    id bigint NOT NULL,
    phone_number character varying(200) NOT NULL,
    number_type character varying(200) NOT NULL,
    dob date,
    id_person bigint NOT NULL
);


ALTER TABLE public.phones OWNER TO postgres;



create table public.integers_as_booleans (
  id bigint not null generated by default as identity primary key,
  done_bool boolean not null default false,
  done_int smallint not null default 0
);

--
-- TOC entry 217 (class 1259 OID 58912)
-- Name: phones_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.phones_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.phones_id_seq OWNER TO postgres;

--
-- TOC entry 2916 (class 0 OID 0)
-- Dependencies: 217
-- Name: phones_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.phones_id_seq OWNED BY public.phones.id;


--
-- TOC entry 218 (class 1259 OID 58914)
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    value character varying
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- TOC entry 2751 (class 2604 OID 58920)
-- Name: articles id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.articles ALTER COLUMN id SET DEFAULT nextval('public.articles_id_seq'::regclass);


--
-- TOC entry 2752 (class 2604 OID 58921)
-- Name: customers id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customers ALTER COLUMN id SET DEFAULT nextval('public.customers_id_seq'::regclass);


--
-- TOC entry 2754 (class 2604 OID 58922)
-- Name: order_details id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.order_details ALTER COLUMN id SET DEFAULT nextval('public.order_details_id_seq'::regclass);


--
-- TOC entry 2755 (class 2604 OID 58923)
-- Name: orders id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.orders ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq'::regclass);


--
-- TOC entry 2756 (class 2604 OID 58924)
-- Name: people id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.people ALTER COLUMN id SET DEFAULT nextval('public.people_id_seq'::regclass);


--
-- TOC entry 2757 (class 2604 OID 58925)
-- Name: phones id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.phones ALTER COLUMN id SET DEFAULT nextval('public.phones_id_seq'::regclass);


--
-- TOC entry 2759 (class 2606 OID 58927)
-- Name: articles articles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.articles
    ADD CONSTRAINT articles_pkey PRIMARY KEY (id);


--
-- TOC entry 2761 (class 2606 OID 58929)
-- Name: customers customers_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_pk PRIMARY KEY (id);


--
-- TOC entry 2765 (class 2606 OID 58931)
-- Name: customers_plain customers_plain_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customers_plain
    ADD CONSTRAINT customers_plain_pk PRIMARY KEY (id);


--
-- TOC entry 2767 (class 2606 OID 58933)
-- Name: customers_with_code customers_with_code_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.customers_with_code
    ADD CONSTRAINT customers_with_code_pkey PRIMARY KEY (code);


--
-- TOC entry 2763 (class 2606 OID 58935)
-- Name: customers with spaces customers_with_spaces_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public."customers with spaces"
    ADD CONSTRAINT customers_with_spaces_pk PRIMARY KEY ("id with spaces");


--
-- TOC entry 2769 (class 2606 OID 58937)
-- Name: order_details order_details_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.order_details
    ADD CONSTRAINT order_details_pkey PRIMARY KEY (id);


--
-- TOC entry 2771 (class 2606 OID 58939)
-- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);


--
-- TOC entry 2773 (class 2606 OID 58941)
-- Name: people people_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.people
    ADD CONSTRAINT people_pkey PRIMARY KEY (id);


--
-- TOC entry 2775 (class 2606 OID 58943)
-- Name: phones phones_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.phones
    ADD CONSTRAINT phones_pkey PRIMARY KEY (id);


--
-- TOC entry 2776 (class 2606 OID 58944)
-- Name: order_details order_details_orders_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.order_details
    ADD CONSTRAINT order_details_orders_fk FOREIGN KEY (id_order) REFERENCES public.orders(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 2777 (class 2606 OID 58949)
-- Name: orders orders_customers_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_customers_fk FOREIGN KEY (id_customer) REFERENCES public.customers(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 2778 (class 2606 OID 58954)
-- Name: phones phones_id_person_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.phones
    ADD CONSTRAINT phones_id_person_fkey FOREIGN KEY (id_person) REFERENCES public.people(id);



-- DROP FUNCTION public.sp_get_customers();

CREATE OR REPLACE FUNCTION public.sp_get_customers()
 RETURNS TABLE(id bigint, code character varying, description character varying, city character varying, rating integer)
 LANGUAGE plpgsql
AS $function$
begin
  return query
  	select 
  		c.id, c.code, c.description, c.city, c.rating
 	from 
 		customers c
 	order by c.description;
end
$function$
;

-- DROP FUNCTION public.sp_get_customers2(varchar, varchar);

CREATE OR REPLACE FUNCTION public.sp_get_customers2(code_filter character varying, city_filter character varying)
 RETURNS TABLE(id bigint, code character varying, description character varying, city character varying, rating integer)
 LANGUAGE plpgsql
AS $function$
begin
  return query
  	select 
  		c.id, c.code, c.description, c.city, c.rating
 	from 
 		customers c
 	order by c.description;
end
$function$
;

-- DROP FUNCTION public.sp_get_int(varchar, varchar);

CREATE OR REPLACE FUNCTION public.sp_get_int(code_filter character varying, city_filter character varying)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
begin
  return 1;
end
$function$
;

-- DROP PROCEDURE public.sp_loggerpro_writer(int4, varchar, varchar, timestamp, int4);

CREATE OR REPLACE PROCEDURE public.sp_loggerpro_writer(IN p_log_type integer, IN p_log_tag character varying, IN p_log_message character varying, IN p_log_timestamp timestamp without time zone, IN p_log_thread_id integer)
 LANGUAGE plpgsql
AS $procedure$
begin
  INSERT INTO 
  	public.loggerpro_logs(log_type, log_tag, log_message, log_timestamp, log_thread_id)
	values (p_log_type, p_log_tag, p_log_message, p_log_timestamp, p_log_thread_id);
end;
$procedure$
;
